﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lemonc.Data
{
    internal class DBAccess<DBConnection, DBCommand, DataParameter, DBDataAdapter, DBCommandBuilder> : IDBAccess where DBConnection : IDbConnection, new() where DBCommand : IDbCommand, new() where DataParameter : IDataParameter, new() where DBDataAdapter : IDbDataAdapter, new() where DBCommandBuilder : DbCommandBuilder, new()
    {
        public IDbConnection connection { get;}

        public IDbTransaction DBTransaction
        {
            get;
            set;
        }
        public DBAccessType DBType
        {
            get;
            set;
        }
        public DBAccess(DBAccessType type, string connectionString)
        {
            this.DBType = type;
            this.connection = ((default(DBConnection) == null) ? Activator.CreateInstance<DBConnection>() : default(DBConnection));
            this.connection.ConnectionString = connectionString;
        }
        public bool OpenConnection()
        {
            bool result = true;
            try
            {
                if (this.connection.State != ConnectionState.Open)
                {
                    this.connection.Open();
                }
            }
            catch (Exception ex)
            {
                result = false;
                throw new Exception("打开数据库失败！信息：" + ex.Message);
            }
            return result;
        }
        public bool CloseConnection()
        {
            bool result = true;
            try
            {
                if (this.connection.State != ConnectionState.Closed)
                {
                    this.connection.Close();
                }
            }
            catch (Exception ex)
            {
                result = false;
                throw new Exception("关闭数据库失败！信息：" + ex.Message);
            }
            return result;
        }
        private DBCommand CreateCommand(string commandText, CommandType type, IDbTransaction dbTransaction, IDataParameter[] paraList)
        {
            DBCommand result = default(DBCommand);
            try
            {
                result = ((default(DBCommand) == null) ? Activator.CreateInstance<DBCommand>() : default(DBCommand));
                result.CommandText = commandText;
                result.CommandType = type;
                result.Connection = this.connection;
                result.Transaction = dbTransaction;
                if (paraList != null)
                {
                    for (int i = 0; i < paraList.Length; i++)
                    {
                        IDataParameter value = paraList[i];
                        result.Parameters.Add(value);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("创建数据库操作的命令对象失败！信息：" + ex.Message);
            }
            return result;
        }
        public IDbCommand CreateDbCommand(string commandText, CommandType type, IDataParameter[] paraList)
        {
            DBCommand dBCommand = default(DBCommand);
            try
            {
                dBCommand = ((default(DBCommand) == null) ? Activator.CreateInstance<DBCommand>() : default(DBCommand));
                dBCommand.CommandText = commandText;
                dBCommand.CommandType = type;
                dBCommand.Connection = this.connection;
                if (paraList != null)
                {
                    for (int i = 0; i < paraList.Length; i++)
                    {
                        IDataParameter value = paraList[i];
                        dBCommand.Parameters.Add(value);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("创建数据库操作的命令对象失败！信息：" + ex.Message);
            }
            return dBCommand;
        }
        public int ExecuteNonQueryByText(string commandText, IDataParameter[] paraList)
        {
            int result = 0;
            try
            {
                DBCommand dBCommand = this.CreateCommand(commandText, CommandType.Text, this.DBTransaction ,paraList);
                this.OpenConnection();
                result = dBCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("执行命令字符串返回影响的行数失败！信息：" + ex.Message);
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        public IDataReader ExecuteReaderByText(string selectCommandText, IDataParameter[] paraList)
        {
            IDataReader result = null;
            
            DBCommand dBCommand = this.CreateCommand(selectCommandText, CommandType.Text, this.DBTransaction, paraList);
            this.OpenConnection();
            result = dBCommand.ExecuteReader(); 
            return result;
        }
        public object ExecuteScalarByText(string selectCommandText, IDataParameter[] paraList)
        {
            object result = null;
            try
            {
                DBCommand dBCommand = this.CreateCommand(selectCommandText, CommandType.Text, this.DBTransaction, paraList);
                this.OpenConnection();
                result = dBCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception("执行命令字符串返回首行首列失败！信息：" + ex.Message);
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        public int ExecuteNonQueryByProc(string procName, IDataParameter[] paraList)
        {
            int result = 0;
            try
            {
                DBCommand dBCommand = this.CreateCommand(procName, CommandType.StoredProcedure, this.DBTransaction, paraList);
                this.OpenConnection();
                result = dBCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("执行存储过程返回影响的行数失败！信息：" + ex.Message);
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        public IDataReader ExecuteReaderByProc(string selectProcName, IDataParameter[] paraList)
        {
            IDataReader result = null;
            try
            {
                DBCommand dBCommand = this.CreateCommand(selectProcName, CommandType.StoredProcedure, this.DBTransaction, paraList);
                this.OpenConnection();
                result = dBCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                this.CloseConnection();
                throw new Exception("执行存储过程返回数据读取器失败！信息：" + ex.Message);
            }
            return result;
        }
        public object ExecuteScalarByProc(string selectProcName, IDataParameter[] paraList)
        {
            object result = null;
            try
            {
                DBCommand dBCommand = this.CreateCommand(selectProcName, CommandType.StoredProcedure, this.DBTransaction, paraList);
                this.OpenConnection();
                result = dBCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception("执行存储过程返回首行首列失败！信息：" + ex.Message);
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }
        public DataTable ExecuteDataTableByText(string selectCommandText, IDataParameter[] paraList)
        {
            DataSet dataSet = new DataSet();
            try
            {
                DBCommand dBCommand = this.CreateCommand(selectCommandText, CommandType.Text, this.DBTransaction, paraList);
                DBDataAdapter dBDataAdapter = (default(DBDataAdapter) == null) ? Activator.CreateInstance<DBDataAdapter>() : default(DBDataAdapter);
                dBDataAdapter.SelectCommand = dBCommand;
                dBDataAdapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                throw new Exception("执行命令字符串返回数据记录集异常！信息：" + ex.Message);
            }
            finally
            {
                this.CloseConnection();
            }
            return dataSet.Tables[0];
        }
        public DataTable ExecuteDataTableByProc(string selectProcName, IDataParameter[] paraList)
        {
            DataSet dataSet = new DataSet();
            try
            {
                DBCommand dBCommand = this.CreateCommand(selectProcName, CommandType.StoredProcedure, this.DBTransaction, paraList);
                DBDataAdapter dBDataAdapter = (default(DBDataAdapter) == null) ? Activator.CreateInstance<DBDataAdapter>() : default(DBDataAdapter);
                dBDataAdapter.SelectCommand = dBCommand;
                dBDataAdapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                throw new Exception("执行存储过程返回数据记录集！信息：" + ex.Message);
            }
            finally
            {
                this.CloseConnection();
            }
            return dataSet.Tables[0];
        }
        public IDbDataAdapter GetDbDataAdapterBySelectSql(string sql, IDataParameter[] paraList)
        {
            DBDataAdapter dBDataAdapter = (default(DBDataAdapter) == null) ? Activator.CreateInstance<DBDataAdapter>() : default(DBDataAdapter);
            DBCommand dBCommand = this.CreateCommand(sql, CommandType.Text, this.DBTransaction, paraList);
            dBDataAdapter.SelectCommand = dBCommand;
            return dBDataAdapter;
        }
        public DbCommandBuilder GetDbCommandBuilder(IDbDataAdapter da)
        {
            DBCommandBuilder dBCommandBuilder = Activator.CreateInstance<DBCommandBuilder>();
            dBCommandBuilder.DataAdapter = (da as DbDataAdapter);
            return dBCommandBuilder;
        }
        public IDataParameter CreateParameter(string name, string fieldName, object value)
        {
            DataParameter dataParameter = default(DataParameter);
            try
            {
                dataParameter = ((default(DataParameter) == null) ? Activator.CreateInstance<DataParameter>() : default(DataParameter));
                dataParameter.ParameterName = name;
                dataParameter.SourceColumn = fieldName;
                dataParameter.Value = value;
            }
            catch (Exception ex)
            {
                throw new Exception("创建数据库操作的命令对象失败！信息：" + ex.Message);
            }
            return dataParameter;
        }
    }
}
